﻿using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Text;

namespace SharpSoft.Data
{
    /// <summary>
    /// 条件语句构建器
    /// </summary>
    public class QueryConditionBuilder
    {
        public QueryConditionBuilder(SQLTextGenerator textGenerator)
        {
            this.sqlTextGenerator = textGenerator;
            this.tree = new QueryConditionTree(textGenerator);
        }
        private readonly SQLTextGenerator sqlTextGenerator;
        private QueryConditionTree tree = null;//条件逻辑树
        private LogicalOperator? lastLogic = null;//记录将要应用的逻辑运算符

        public SQLTextGenerator TextGenerator() => this.sqlTextGenerator;

        public override string ToString()
        {
            if (tree == null)
            {
                return "(1 = 1)";
            }
            return tree.ToText();
        }

        public string ToText()
        {
            return this.ToString();
        }
        /// <summary>
        /// 获取数据库脚本的安全名称
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public string safeName(string name)
        {
            return this.sqlTextGenerator.SafetiyName(name);
        }

        /// <summary>
        /// 使用上一次标记的逻辑运算符，使用后将其置空。
        /// </summary>
        /// <returns></returns>
        [DebuggerStepThrough]
        private LogicalOperator useLastLogicOperator()
        {
            if (lastLogic.HasValue)
            {
                var temp = lastLogic.Value;
                lastLogic = null;
                return temp;
            }
            else
            {
                throw new Exception("未指明要使用逻辑运算符，请在加入条件时先使用“and()”或“or()”方法来指明逻辑运算符。");
            }
        }

        /// <summary>
        /// 增加一个一般条件比较查询
        /// </summary>
        /// <param name="left">比较的左边表达式，按原样输出</param>
        /// <param name="opr">比较运算符</param>
        /// <param name="right">比较的右边表达式，如果是<see cref="string"/>类型，按原样输出。如果是其他类型则将其转换为对应的TSQL值表达形式后输出。如需输出一个TSQL中的字符串则传入一个new SqlValue(str)</param>
        /// <returns></returns>
        [DebuggerStepThrough]
        public QueryConditionBuilder Normal(string left, ComparisonOperator opr, object right, bool keepStr = false)
        {
            string rstr;
            if (right is string str && (keepStr || str.StartsWith("@")))
            {//以@开头的参数名保持原样
                rstr = str;
            }
            else
            {
                var sqlval = new SqlValue(right);
                rstr = sqlval.ToTSqlText();
            }
            var con = new QueryCondition(this.sqlTextGenerator) { Left = left, Operator = opr, Right = rstr };
            this.Put(con);
            return this;
        }
        /// <summary>
        /// 增加一个查询条件
        /// </summary>
        /// <param name="queryCondition"></param>
        /// <returns></returns>
        public QueryConditionBuilder Put(IQueryCondition queryCondition)
        {
            if (this.tree.Left == null)
            {//左边为空，直接放置在左边且丢弃逻辑运算符
                this.tree.Left = queryCondition;
            }
            else if (this.tree.Right == null)
            {//右边为空，置入逻辑运算符和右边
                LogicalOperator opr = useLastLogicOperator();
                this.tree.Operator = opr;
                this.tree.Right = queryCondition;
            }
            else
            {//两边已满,当前逻辑树压入左侧下一层
                LogicalOperator opr = useLastLogicOperator();
                var newtree = new QueryConditionTree(sqlTextGenerator) { Left = this.tree, Operator = opr, Right = queryCondition };
                this.tree = newtree;
            }
            return this;
        }
        /// <summary>
        /// 连接另外一个查询条件
        /// </summary>
        /// <param name="action"></param>
        /// <returns></returns>
        public QueryConditionBuilder other(Action<QueryConditionBuilder> action)
        {
            if (this.tree.Left == null)
            {
                throw new Exception("当前查询条件的逻辑树为空，无法连接另外一个查询条件。");
            }
            var qcb = new QueryConditionBuilder(this.sqlTextGenerator);
            action?.Invoke(qcb);
            var left = this.tree;
            var right = qcb.tree;
            this.tree = new QueryConditionTree(sqlTextGenerator) { Left = left, Right = right, Operator = useLastLogicOperator() };
            return this;
        }

        /// <summary>
        /// 设置连接下一个条件的逻辑运算符为AND
        /// </summary>
        /// <returns></returns>
        public QueryConditionBuilder and()
        {
            lastLogic = LogicalOperator.And;
            return this;
        }
        /// <summary>
        /// 设置连接下一个条件的逻辑运算符为OR
        /// </summary>
        /// <returns></returns>
        public QueryConditionBuilder or()
        {
            lastLogic = LogicalOperator.Or;
            return this;
        }
        /// <summary>
        /// 添加一个原样输出的查询条件
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public QueryConditionBuilder original(string sql)
        {
            this.Put(new OriginalCondition() { Sql = sql });
            return this;
        }
        /// <summary>
        /// 增加一个相等比较
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder equal(string left, object right)
        {
            if (right == null || right == DBNull.Value)
            {
                return isnull(left);
            }
            this.Normal(left, ComparisonOperator.Equal, right);
            return this;
        }
        /// <summary>
        /// 增加一个不等比较
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder unequal(string left, object right)
        {
            if (right == null || right == DBNull.Value)
            {
                return isnotnull(left);
            }
            this.Normal(left, ComparisonOperator.Unequal, right);
            return this;
        }
        /// <summary>
        /// 增加一个大于比较
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder greater(string left, object right)
        {
            this.Normal(left, ComparisonOperator.Greater, right);
            return this;
        }
        /// <summary>
        /// 增加一个小于比较
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder less(string left, object right)
        {
            this.Normal(left, ComparisonOperator.Less, right);
            return this;
        }
        /// <summary>
        /// 增加一个大于或等于比较
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder greaterOrEqual(string left, object right)
        {
            this.Normal(left, ComparisonOperator.GreaterOrEqual, right);
            return this;
        }
        /// <summary>
        /// 增加一个小于或等于比较
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder lessOrEqual(string left, object right)
        {
            this.Normal(left, ComparisonOperator.LessOrEqual, right);
            return this;
        }
        /// <summary>
        /// 增加一个包含查询
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder contains(string left, string right)
        {
            this.Normal(left, ComparisonOperator.Contains, new SqlValue($"%{right}%"));
            return this;
        }
        /// <summary>
        /// 增加一个以指定字符串开始的查询
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder startwith(string left, string right)
        {
            this.Normal(left, ComparisonOperator.StartWith, new SqlValue($"{right}%"));
            return this;
        }
        /// <summary>
        /// 增加一个以指定字符串结尾的比较
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder endwith(string left, string right)
        {
            this.Normal(left, ComparisonOperator.EndWith, new SqlValue($"%{right}"));
            return this;
        }
        /// <summary>
        /// 增加一个IN查询
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder inlist<T>(string left, T[] right)
        {
            if (right != null)
            {
                var rightstr = SqlUtility.ConvertToTSqlText(right);
                this.Normal(left, ComparisonOperator.In, rightstr, true);//这里传入的字符串保持原样
            }
            return this;
        }
        /// <summary>
        /// 增加一个IN查询
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public QueryConditionBuilder inlist(string left, IEnumerable<object> right)
        {
            if (right != null)
            {
                var rightstr = SqlUtility.ConvertToTSqlText(right);
                this.Normal(left, ComparisonOperator.In, rightstr);
            }
            return this;
        }
        /// <summary>
        /// 增加一个判断对象为NULL的查询
        /// </summary>
        /// <param name="left"></param>
        /// <returns></returns>
        public QueryConditionBuilder isnull(string left)
        {
            var con = new NullAssertCondition(this.sqlTextGenerator) { Left = left, Negation = false };
            this.Put(con);
            return this;
        }
        /// <summary>
        /// 增加一个判断对象不为NULL的查询
        /// </summary>
        /// <param name="left"></param>
        /// <returns></returns>
        public QueryConditionBuilder isnotnull(string left)
        {
            var con = new NullAssertCondition(this.sqlTextGenerator) { Left = left, Negation = true };
            this.Put(con);
            return this;
        }
    }

    public interface IQueryCondition
    {
        string ToText();
    }
    /// <summary>
    /// 一般查询条件
    /// </summary>
    public class QueryCondition : IQueryCondition
    {
        internal QueryCondition(SQLTextGenerator textGenerator)
        {
            this.sqlTextGenerator = textGenerator;
        }
        private readonly SQLTextGenerator sqlTextGenerator;
        /// <summary>
        /// 条件左边表达式
        /// </summary>
        public string Left { get; set; }
        /// <summary>
        /// 比较运算符
        /// </summary>
        public ComparisonOperator Operator { get; set; }
        /// <summary>
        /// 条件右边表达式
        /// </summary>
        public object Right { get; set; }

        public string ToText()
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(Left);
            sb.Append(" ");
            sb.Append(GetComparisonOperatorText(this.Operator));
            sb.Append(" ");
            sb.Append(Right);

            return sb.ToString();
        }
        public override string ToString()
        {
            return this.ToText();
        }

        public static string GetComparisonOperatorText(ComparisonOperator opr)
        {
            string c;
            switch (opr)
            {
                case ComparisonOperator.Equal:
                    c = "=";
                    break;
                case ComparisonOperator.Unequal:
                    c = "!=";
                    break;
                case ComparisonOperator.Greater:
                    c = ">";
                    break;
                case ComparisonOperator.Less:
                    c = "<";
                    break;
                case ComparisonOperator.GreaterOrEqual:
                    c = ">=";
                    break;
                case ComparisonOperator.LessOrEqual:
                    c = "<=";
                    break;
                case ComparisonOperator.Contains:
                    c = "LIKE";
                    break;
                case ComparisonOperator.StartWith:
                    c = "LIKE";
                    break;
                case ComparisonOperator.EndWith:
                    c = "LIKE";
                    break;
                case ComparisonOperator.In:
                    c = "IN";
                    break;
                default:
                    throw new NotSupportedException();
            }
            return c;
        }

    }
    /// <summary>
    /// 条件树
    /// </summary>
    public class QueryConditionTree : IQueryCondition
    {
        internal QueryConditionTree(SQLTextGenerator textGenerator)
        {
            this.sqlTextGenerator = textGenerator;
        }
        private readonly SQLTextGenerator sqlTextGenerator;
        /// <summary>
        /// 左边的表达式
        /// </summary>
        public IQueryCondition Left { get; set; }
        /// <summary>
        /// 逻辑运算符
        /// </summary>
        public LogicalOperator Operator { get; set; }
        /// <summary>
        /// 右边的表达式
        /// </summary>
        public IQueryCondition Right { get; set; }

        public string ToText()
        {
            if (Left == null)
            {
                return "(1 = 1)";
            }
            if (Right == null)
            {
                return $"({Left})";
            }
            return $"({Left}) {GetLogicalOperatorText(this.Operator)} ({Right})";
        }

        public override string ToString()
        {
            return ToText();
        }
        public static string GetLogicalOperatorText(LogicalOperator opr)
        {
            string c;
            switch (opr)
            {
                case LogicalOperator.And:
                    c = "AND";
                    break;
                case LogicalOperator.Or:
                    c = "OR";
                    break;
                default:
                    throw new NotSupportedException();
            }
            return c;
        }
    }

    /// <summary>
    /// 判断值是否为null
    /// </summary>
    public class NullAssertCondition : IQueryCondition
    {
        internal NullAssertCondition(SQLTextGenerator textGenerator)
        {
            this.sqlTextGenerator = textGenerator;
        }
        private readonly SQLTextGenerator sqlTextGenerator;

        public string Left { get; set; }

        public bool Negation { get; set; } = false;

        public string ToText()
        {
            return $"{Left} {(this.Negation ? this.sqlTextGenerator.NotNullAssert : this.sqlTextGenerator.NullAssert)}";
        }
        public override string ToString()
        {
            return this.ToText();
        }
    }
    /// <summary>
    /// 保持原样
    /// </summary>
    public class OriginalCondition : IQueryCondition
    {
        public string Sql { get; set; }
        public string ToText()
        {
            return this.Sql;
        }
        public override string ToString()
        {
            return this.ToText();
        }
    }

    /// <summary>
    /// 比较运算符
    /// </summary>
    public enum ComparisonOperator
    {
        Equal,
        Unequal,
        Greater,
        Less,
        GreaterOrEqual,
        LessOrEqual,
        /// <summary>
        /// 模糊查询
        /// </summary>
        Contains,
        /// <summary>
        /// 以……开始
        /// </summary>
        StartWith,
        /// <summary>
        /// 以……结束
        /// </summary>
        EndWith,
        /// <summary>
        /// 列表匹配
        /// </summary>
        In
    }

    public enum LogicalOperator
    {
        And,
        Or
    }
}
